package cn.com.dashihui.web.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.jfinal.aop.Before;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.jfinal.plugin.activerecord.tx.Tx;

import cn.com.dashihui.kit.ExcelExportUtil;
import cn.com.dashihui.kit.ExcelExportUtil.Pair;
import cn.com.dashihui.web.dao.Category;
import cn.com.dashihui.web.dao.DetailsImg;
import cn.com.dashihui.web.dao.GoodsBaseImages;
import cn.com.dashihui.web.dao.GoodsEge;
import cn.com.dashihui.web.dao.GoodsItem;
import cn.com.dashihui.web.dao.GoodsSaleItem;
import cn.com.dashihui.web.dao.Seller;
import cn.com.dashihui.web.dao.WxGoodsTitle;
public class ExchangeGoodsService {
	/**
	 * 分页查找商品
	 * @param pageNum
	 * @param pageSize
	 */
	public Page<Record> findByPage(int pageNum,int pageSize,int sGoodId,String sGoodName,int state,int sCategoryonid,int sCategorytwid,int sCategorythid,int sCategoryfoid){
		StringBuffer sBuffer = new StringBuffer("FROM t_bus_ege_goods AS A "
				+ "LEFT JOIN t_bus_ege_goods_spec B on A.id=B.goodId "
				+ "LEFT JOIN t_dict_category D on A.categoryonid=D.categoryId "
				+ "LEFT JOIN t_dict_category E on A.categorytwid=E.categoryId "
				+ "LEFT JOIN t_dict_category G on A.categorythid=G.categoryId "
				+ "LEFT JOIN t_dict_category H on A.categoryfoid=H.categoryId "
				+ "where 1=1");
		List<Object> params = new ArrayList<Object>();

		if(sGoodId!=0){
			sBuffer.append(" AND A.id=?");
			params.add(sGoodId);
		}
		if(StrKit.notBlank(sGoodName)){
			sBuffer.append(" AND A.name like ?");
			params.add("%"+sGoodName+"%");
		}
		if(sCategoryfoid!=0){
			sBuffer.append(" AND A.categoryonid=? AND A.categorytwid=? AND A.categorythid=? AND A.categoryfoid=?");
			params.add(sCategoryonid);params.add(sCategorytwid);params.add(sCategorythid);params.add(sCategoryfoid);
		}else if(sCategorythid!=0){
			sBuffer.append(" AND A.categoryonid=? AND A.categorytwid=? AND A.categorythid=?");
			params.add(sCategoryonid);params.add(sCategorytwid);params.add(sCategorythid);
		}else if(sCategorytwid!=0){
			sBuffer.append(" AND A.categoryonid=? AND A.categorytwid=?");
			params.add(sCategoryonid);params.add(sCategorytwid);
		}else if(sCategoryonid!=0){
			sBuffer.append(" AND A.categoryonid=?");
			params.add(sCategoryonid);
		}
		switch (state) {
			case 1:
				sBuffer.append(" AND A.pstate=1 and A.state=1");
				break;
			case 2:
				sBuffer.append(" AND A.pstate=1 and A.state=2");
				break;
			case 3:
				sBuffer.append(" AND A.pstate=1 and A.state=3");
				break;
			case 4:
				sBuffer.append(" AND A.pstate=2");
				break;
			case 5:
				sBuffer.append(" AND A.pstate=3");
				break;
		}
		/*if(!StrKit.isBlank(keyword)){
			sBuffer.append(" AND A.name LIKE ?");
			params.add("%"+keyword+"%");
		}*/
		sBuffer.append(" group by A.id");
		sBuffer.append(" ORDER BY A.createDate DESC");
		 
		//return Db.paginate(pageNum, pageSize, "SELECT A.*, C.title,D.categoryName as oneName,E.categoryName as twoName", sBuffer.toString(), params.toArray());
		Page<Record> page=	Db.paginate(pageNum, pageSize, "SELECT A.*,B.id as sid, D.categoryName as oneName,E.categoryName as twoName", sBuffer.toString(), params.toArray());
			
	   
		return  page;
	}
	/**
	 * 查找商品
	 */
	public GoodsEge findById(int id){
		return GoodsEge.me().findFirst("SELECT A.*,D.categoryName as oneName,E.categoryName as twoName "
				+ "FROM t_bus_ege_goods AS A  "
				+ "LEFT JOIN t_dict_category D on A.categoryonid=D.categoryId LEFT JOIN t_dict_category E on A.categorytwid=E.categoryId where A.id=?",id);
	}
	public GoodsEge findByIdGoods(int id){
		return GoodsEge.me().findFirst("SELECT * from  t_bus_ege_goods where id=?",id);
	}
	/**
	 * 根据商品名字获取商品ID  按时间排序倒序第一个
	 * @param name
	 * @return
	 */
	public int findidByName(String name){
		return GoodsEge.me().findFirst("SELECT * from  t_bus_ege_goods where name=? order by createDate desc",name).get("id");
	}
	/**
	 * 增加商品
	 */
	public boolean add(GoodsEge goods){
		return goods.save();
	}
	/**
	 *修改商品
	 */
	public boolean updategood(GoodsEge goods){
		return goods.update();
	}
	/**
	 * 轮播图片排序
	 * @param sortMap
	 * @return
	 */
	public boolean sortImages(Map<String,String> sortMap){
		int batchSize = sortMap.size();
		List<String> sqlList = new ArrayList<String>();
		for(String id : sortMap.keySet()){
			sqlList.add("UPDATE t_bus_goods_base_images SET orderNo="+sortMap.get(id)+" WHERE id="+id);
		}
		int[] result = Db.batch(sqlList,batchSize);
		return result.length>0;
	}
	
	/**
	 * 详情图片排序
	 * @param sortMap
	 * @return
	 */
	public boolean sortDetailImages(Map<String,String> sortMap){
		int batchSize = sortMap.size();
		List<String> sqlList = new ArrayList<String>();
		for(String id : sortMap.keySet()){
			sqlList.add("UPDATE goods_details_images SET orderNo="+sortMap.get(id)+" WHERE id="+id);
		}
		int[] result = Db.batch(sqlList,batchSize);
		return result.length>0;
	}
	
	/**
	 * 商品图片列表
	 * 
	 * @return
	 */
	public List<GoodsBaseImages> findAllImages(String LunBoNum){
		return GoodsBaseImages.me().find("SELECT A.* FROM t_bus_goods_base_images A WHERE A.LunBoNum=? ORDER BY A.orderNo",LunBoNum);
	}
	/**
	 * 根据商品id查询所有的扩展信息
	 * @param iid
	 * @return
	 */
	public List<GoodsSaleItem> findAllItems(int goodsid){
		return GoodsSaleItem.me().find("SELECT * from  t_bus_goods_sale_items  where goodsid=?",goodsid);
	}
	/**
	 * 删除当前商品对应的扩展信息
	 * @param goodId
	 * @return
	 */
	public int deleteSaleItem(int goodsid) {
		return Db.update("delete from t_bus_goods_sale_items where goodsid=?",goodsid);
	}
	/**
	 * 商品详情图片列表
	 * 
	 * @return
	 */
	public List<DetailsImg> findDetailImages(String LunBoNum){
		return DetailsImg.me().find("SELECT A.* FROM goods_details_images A WHERE A.LunBoNum=? ORDER BY A.orderNo",LunBoNum);
	}
	
	
	/**
	 * 添加图片
	 * @param newObject
	 * @return
	 */
	public boolean addImage(GoodsBaseImages newObject){
		return newObject.save();
	}
	
	
	/**
	 * 添加详情图片
	 * @param newObject
	 * @return
	 */
	public boolean addDetailImage(DetailsImg newObject){
		return newObject.save();
	}
	
	
	/**
	 * 删除图片
	 * @param id
	 * @return
	 */
	public boolean delImage(int id){
		return GoodsBaseImages.me().deleteById(id);
	}
	
	
	/**
	 * 删除详情图片
	 * @param id
	 * @return
	 */
	public boolean delDetailImage(int id){
		return DetailsImg.me().deleteById(id);
	}
	
	
	/**
	 * 设置图片为商品logo
	 * @param id
	 * @return
	 */
	@Before(Tx.class)
	public boolean setImageLogo(int goodsid,int id){
		List<String> sqlList = new ArrayList<String>();
		//更新该商品所有LOGO的isLogo标识为0
		sqlList.add("UPDATE t_bus_goods_base_images SET isLogo=0 WHERE goodsid="+goodsid);
		//更新当前LOGO的isLogo标识为1
		sqlList.add("UPDATE t_bus_goods_base_images SET isLogo=1 WHERE goodsid="+goodsid+" AND id="+id);
		//更新该商品的LOGO字段值为当前LOGO的地址
		sqlList.add("UPDATE t_bus_goods_base SET thumb=(SELECT thumb FROM t_bus_goods_base_images WHERE id="+id+") WHERE id="+goodsid);
		Db.batch(sqlList, sqlList.size());
		return true;
	}
	public int doCancel(String id, String checktime) {
		return Db.update("update t_bus_ege_goods set state=3 ,checktime=? where id=?",checktime,id);
	}
	public int doAccept(String id, String checktime) {
		return Db.update("update t_bus_ege_goods set state=2 ,checktime=? where id=?",checktime,id);
	}
	public boolean delete(GoodsEge goods) {
		return goods.delete();
	}
	public int doDown(String id, String downtime) {
		return Db.update("update t_bus_ege_goods set state=3 ,pstate=1,downtime=? where id=?",downtime,id);
	}
	public int doUp( String id,String createDate ) {
		return Db.update("update t_bus_ege_goods set state=2 ,pstate=1 ,createDate=? where id=?",createDate,id);
	}
	/**
	 * @param id
	 * @return
	 */
	public int doclerk( String id ) {
		return Db.update("update t_bus_ege_goods set pstate=2 ,stock=0 where id=?",id);
	}
	public int tooDown(String id, String checktime) {
		return Db.update("update t_bus_ege_goods set state=5 ,checktime=? where id=?",checktime,id);
	}
	public Record findGoodDetailById(int id) {
			return Db.findFirst("select * from t_bus_ege_goods where id=?",id);
	}
	public List<Record> getGoodsItem(String gooditem) {
		return Db.find("select * from t_bus_goods_item where goodItem=?",gooditem);
	}
	
	/**
	 * 删除规格
	 * @param goodId
	 * @return
	 */
	public int deteleSpec(int goodId) {
		return Db.update("delete from t_bus_ege_goods_spec where goodId=?",goodId);
	}
	/**
	 * 
	 * @param gooditem
	 * @return
	 */
	public List<Record> getGoodSpec(int goodsId) {
		return Db.find("select * from t_bus_ege_goods_spec where goodId=?",goodsId);
	}
	public int getGoodsItemid(int id) {
		return Db.queryInt("select B.id FROM t_bus_ege_goods AS A  inner JOIN  t_bus_goods_item AS B ON A.goodItem=B.goodItem where A.id=？",id);
	}
	public int deteleItem(String goodItem) {
		return Db.update("delete from t_bus_goods_item where goodItem=?",goodItem);
	}
	/**
	 * 删除商品的规格
	 * @param goodId
	 * @return
	 */
	public int deteleGoodSpec(int goodId) {
		return Db.update("delete from t_bus_ege_goods_spec where goodId=?",goodId);
	}


	public Seller getSellerId(int userid) {
		return Seller.me().findFirst("select sellerId from t_bus_seller where userid=?",userid);
	}
	public void addItem(GoodsItem goodsitem) {
		goodsitem.save();
	}
	

	public void updateItem(GoodsItem goodsitem) {
		goodsitem.update();
	}
	
	
	
	/**
	 * 查询公司的状态是否通过
	 * @return
	 */
	public Record findState(int userid,int RoleId) {
		// TODO Auto-generated method stub
		if(RoleId==1||RoleId==2){
			return Db.findFirst("SELECT  * from  t_bus_seller where userid=? ",userid);
		}else{
			return Db.findFirst("SELECT  * from  t_dict_store where storeid=(select storeid from t_auth_admin_seller where id=?)",userid);
		}
	}
	
	public String findselleid(int userid){
		
		return Db.queryStr("SELECT  sellerId from  t_bus_seller where userid=? ",userid);
	}
	
	/**
	 * 查询分公司的开店状态是否正常
	 * @return
	 */
	public Record findQstate(int userid) {
		return Db.findFirst("SELECT  qstate from  t_dict_store where storeid=(select storeid from t_auth_admin_seller where id=?)",userid);
	}
	/**
	 * 从对象中获取公司sellerid
	 */
	public Record FindById(int userId) {
		return Db.findFirst("select * from t_bus_seller where userId=?",userId);
	}
	public Record findByUserId(int userid){
		return Db.findFirst("select sellerId from t_bus_seller where userid=?",userid);
	}
	/**
	 * 分公司Id查所属公司
	 * @param storeid
	 * @return
	 */
	public Record findBysellerId(String storeid){
		return Db.findFirst("select sellerId from t_dict_store where storeid = ?",storeid);
	}
	
	/**
	 * 根据userid,获取角色
	 * @param userid
	 * @return
	 */
	public int findByRoleId(int userid){
		return Db.queryInt("SELECT C.id  from t_auth_admin_seller as A inner JOIN  t_auth_admin_roles_seller as B ON A.id=B.userid  INNER JOIN t_auth_roles_seller AS C ON B.roleid = C.id where userid=?" ,userid);
	}
	/**
	 * 查询分公司的状态是否通过
	 * @return
	 */
	public Record findStoreState(int userid) {
		// TODO Auto-generated method stub
		return Db.findFirst("SELECT  A.storeid from  t_dict_store as A inner JOIN t_auth_admin_seller as B ON A.storeid=B.storeid where B.id=?",userid);
		
	}
	/**
	 * 导出数据
	 * @param response
	 * @param request
	 * @param listz 
	 * @param titles2 
	 * @param member
	 */
	public void export(HttpServletResponse response, HttpServletRequest request, List<Pair> titles, List<Record> listz) {
		ExcelExportUtil.exportByRecord(response, request, "商品上架列表", titles, listz);
	}
	
	
	
	/**
	 * 根据id查询参数名称
	 * @param id
	 * @return
	 */
	public String findOptionNameByid(int id){
		return Category.me().findFirst("SELECT o.optionName from  t_bus_goods_sale_option o where id=?",id).get("optionName");
	}
	/**
	 * 查询所有的标签
	 * @return
	 */
	public List<WxGoodsTitle> findAllTitle(){
		return WxGoodsTitle.me().find("SELECT A.* FROM t_bus_goods_title A  ORDER BY A.id DESC");
	}
	/**
	 * 根据id查询标签
	 * @return
	 */
	public WxGoodsTitle findTitleById(int lableId){
		return WxGoodsTitle.me().findFirst("SELECT * FROM t_bus_goods_title WHERE id=?",lableId);
	}
	/**
	 * 查询所有的一级category
	 * @param iid
	 * @return
	 */
	public List<Category> finOneCategory(){
		return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=1");
	}
	/**
	 * 查询对应的二级category
	 * @param iid
	 * @return
	 */
	public List<Record> finTwoCategory(String categoryonid) {
		return Db.find("select categoryId,categoryName from t_dict_category where categoryFatherId in ('"+categoryonid+"')");
	}
	/**
	 * 查询对应的三级category
	 * @param iid
	 * @return
	 */
	public List<Record> finThreeCategory(String categorytwid) {
		return Db.find("select categoryId,categoryName from t_dict_category where categoryFatherId in ('"+categorytwid+"')");
	}
	/**
	 * 查询对应的四级category
	 * @param iid
	 * @return
	 */
	public List<Record> finFourCategory(String categorythid) {
		return Db.find("select categoryId,categoryName from t_dict_category where categoryFatherId in ('"+categorythid+"')");
	}
	/**
	 * 查询所有的二级category
	 * @param iid
	 * @return
	 */
	public List<Category> findTwoCategory(){
		return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=2");
	}
	/**
	 * 查询所有的三级category
	 * @param iid
	 * @return
	 */
	public List<Category> findThreeCategory(){
		return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=3");
	}
	/**
	 * 查询所有的四级category
	 * @param iid
	 * @return
	 */
	public List<Category> findFourCategory(){
		return Category.me().find("SELECT * FROM  t_dict_category  WHERE categoryType=4");
	}
	

}